
CREATE DATABASE ksc_event
GO
USE ksc_event
GO
CREATE TABLE [Role](
	Id INT IDENTITY PRIMARY KEY,
	Name VARCHAR(100)
)
GO
CREATE TABLE Account(
	Id INT IDENTITY PRIMARY KEY,
	Role_Id INT FOREIGN KEY REFERENCES [Role](Id),
	Username VARCHAR(100),
	[Password] VARCHAR(300),
	Email VARCHAR(200)
)
GO
CREATE TABLE Activity(
	Id INT IDENTITY PRIMARY KEY,
	Name NVARCHAR(100),
)
GO
CREATE TABLE [Event](
	Id INT IDENTITY PRIMARY KEY,
	Name NVARCHAR(1000),
	Activity_Id INT FOREIGN KEY REFERENCES Activity(Id),
	Introduction NVARCHAR(2000),
	Conduct_Time DATETIME,
	Created_Date DATETIME,
	Fee NVARCHAR(1000),
	Speaker NVARCHAR(1000),
	Guest NVARCHAR(1000),
	Prize NVARCHAR(1000),
	[Address] NVARCHAR(1000),
	[Procedure] NVARCHAR(1000),
	Term NVARCHAR(1000),
	Criteria NVARCHAR(1000),
)
GO
CREATE TABLE Enroll(
	Id INT IDENTITY PRIMARY KEY,
	Account_Id INT FOREIGN KEY REFERENCES Account(Id),
	Event_Id INT FOREIGN KEY REFERENCES [Event](Id),
	[Status] INT,
	Reason NVARCHAR(2000),
	Introduction NVARCHAR(2000),
	Created_Date DATETIME,
	Payment_Option INT,
	Presentation_File VARCHAR(1000)
)
GO
CREATE TABLE News(
	Id INT IDENTITY PRIMARY KEY,
	Event_Id INT FOREIGN KEY REFERENCES [Event](Id),
	Content NVARCHAR(2000)
)
GO
CREATE TABLE Subcribe(
	Id INT IDENTITY PRIMARY KEY,
	Account_Id INT FOREIGN KEY REFERENCES Account(Id),
	Event_Id INT FOREIGN KEY REFERENCES [Event](Id),
)
INSERT INTO Activity VALUES('Seminars')
INSERT INTO Activity VALUES('Symposiums')
INSERT INTO Activity VALUES('Paper presentation')

INSERT INTO [Role] VALUES ('admin')
INSERT INTO [Role] VALUES ('member')

INSERT INTO Account VALUES (1,'admin','123456','admin@kscevent.com')